﻿using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Windows.Forms;
using System.Data.SqlClient;

namespace SeparateProjects
{
    public partial class Pro_Admin_Increase_Score : Form
    {
        //连接数据库
        SqlConnection coon = new SqlConnection(@"Data Source = DESKTOP-28VSNS5;Initial Catalog = Training_Project; 
                                                Uid = Lv; Pwd = 123456;");
        #region 传递必须条件
        /// <summary>
        /// 构造传参
        /// </summary>
        /// <param name="Sid"></param>
        public Pro_Admin_Increase_Score(int Sid)
        {
            InitializeComponent();
            this.txt_Number.Text = Sid.ToString();
            this.txt_Number.Enabled = false;
        }
        #endregion
        #region  加载用户数据
        /// <summary>
        /// 加载数据信息
        /// </summary>
        /// <param name="sender"></param>
        /// <param name="e"></param>
        private void Pro_Admin_Increase_Score_Load(object sender, EventArgs e)
        {
            try
            {
                coon.Open();
                var sql = string.Format(@"select S.Stu_StuInfo_Name,G.Grede_Name
                                          from Stu_Login L
                                          inner join Stu_StuInfo S on(L.Sid = S.Sid)
                                          inner join Grade G on(S.Stu_StuInfo_Grade = G.Stu_StuInfo_Grade)
                                          where L.UserName = '{0}'", this.txt_Number.Text.Trim());
                SqlCommand comm = new SqlCommand(sql, coon);
                SqlDataReader dr = comm.ExecuteReader();
                while (dr.Read())
                {
                    this.txt_Name.Text = Convert.ToString(dr["Stu_StuInfo_Name"]);
                    this.txt_Grade.Text = Convert.ToString(dr["Grede_Name"]);
                    this.txt_Name.Enabled = false;
                    this.txt_Grade.Enabled = false;
                }
                dr.Close();
            }
            catch (Exception ex)
            {
                MessageBox.Show("服务器异常，请联系管理员" + ex.Message);
            }
            finally
            {
                coon.Close();
            }
            Lesson();
            Course_Number();
        }
        #endregion
        #region  加载课程信息
        /// <summary>
        /// 加载课程信息
        /// </summary>
        public void Lesson()
        {
            var Grade = this.txt_Grade.Text;
            var sql = string.Format(@"select C.Course_Num,C.Course_Name from Grade G
                                     inner join Course C on(C.Stu_StuInfo_Grade = G.Stu_StuInfo_Grade)
                                     where G.Grede_Name = '{0}'", Grade);
            DataSet ds = new DataSet();
            SqlDataAdapter adp = new SqlDataAdapter(sql, coon);
            adp.Fill(ds);
            this.cho_Course.DisplayMember = "Course_Name";//面向用户
            this.cho_Course.ValueMember = "Course_Num";//面向开发者
            this.cho_Course.DataSource = ds.Tables[0];
            //是否允许自动创建列
            this.dataGridView1.AutoGenerateColumns = false;
            //不允许出现空行
            this.dataGridView1.AllowUserToAddRows = false;
            //整行选中
            this.dataGridView1.SelectionMode = DataGridViewSelectionMode.FullRowSelect;
        }
        #endregion
        #region  添加成绩到表中
        /// <summary>
        /// 点击添加成绩
        /// </summary>
        /// <param name="sender"></param>
        /// <param name="e"></param>
        private void btn_Increase_Click(object sender, EventArgs e)
        {
            var Name = this.txt_Name.Text;
            var Number = this.txt_Number.Text;
            string sql = string.Format(@" select Course_Name from Course
		                                where Course_Num={0}", Convert.ToInt32(this.cho_Course.SelectedValue));
            var kemu = Course_Name(sql);
            int index = this.dataGridView1.Rows.Add();
            dataGridView1.Rows[index].Cells[0].Value = Number;
            dataGridView1.Rows[index].Cells[1].Value = Name;
            dataGridView1.Rows[index].Cells[2].Value = this.cho_Course.SelectedValue;
            dataGridView1.Rows[index].Cells[3].Value = kemu;
            dataGridView1.Rows[index].Cells[4].Value = this.txt_Score.Text;
            this.txt_Score.ResetText();

        }
        #endregion
        #region  从数据库获取科目名称
        /// <summary>
        /// 获取科目名称
        /// </summary>
        public object Course_Name(string sql)
        {
            try
            {
                coon.Open();
                SqlCommand comm = new SqlCommand(sql, coon);
                object obj = comm.ExecuteScalar();
                return obj;
            }
            catch (Exception)
            {
                return null;
            }
            finally
            {
                coon.Close();
            }
        }
        #endregion
        #region  数据信息提交到数据库
        /// <summary>
        /// 提交到数据库
        /// </summary>
        /// <param name="sender"></param>
        /// <param name="e"></param>
        private void Turn_Click(object sender, EventArgs e)
        {
            var Sid = this.lbl_number.Text;
            try
            {
                //提交到数据库
                coon.Open();
                StringBuilder sb = new StringBuilder();
                sb.Append(@"insert into Score(Sid,Course_Num,Score)
                        values ");
                for (var i = 0; i < dataGridView1.Rows.Count; i++)
                {
                    if (i.Equals(0))
                    {
                        //单行提交
                        sb.AppendFormat("('{0}',{1},{2})", Convert.ToInt32(Sid), Convert.ToInt32(dataGridView1.Rows[i].Cells[2].Value), Convert.ToInt32(dataGridView1.Rows[i].Cells[4].Value));
                    }
                    else
                    {
                        //多行提交
                        sb.AppendFormat(",('{0}',{1},{2})", Convert.ToInt32(Sid), Convert.ToInt32(dataGridView1.Rows[i].Cells[2].Value), Convert.ToInt32(dataGridView1.Rows[i].Cells[4].Value));
                    }
                }
                SqlCommand comm = new SqlCommand(sb.ToString(), coon);
                int effect = comm.ExecuteNonQuery();
                if (effect > 0)
                {
                    DialogResult dr = MessageBox.Show("成绩添加成功","提示信息",MessageBoxButtons.OKCancel,MessageBoxIcon.Information);
                    if (dr.Equals(DialogResult.OK))
                    {
                        this.Hide();
                    }
                }
                else
                {
                    MessageBox.Show("成绩添加失败");
                }
            }
            catch (Exception ex)
            {
                MessageBox.Show("服务器异常，请联系管理员   " + ex.Message);
            }
            finally
            {
                coon.Close();
            }
        }
        #endregion
        #region  获取用户唯一编号
        /// <summary>
        /// 获取唯一值
        /// </summary>
        public void Course_Number()
        {
            var Number = this.txt_Number.Text;
            try
            {
                coon.Open();
                var sql = string.Format(@"select Sid from Stu_Login
                                            where UserName = '{0}'", Number);
                var comm = new SqlCommand(sql, coon);
                SqlDataReader dr = comm.ExecuteReader();
                while (dr.Read())
                {
                    //读取用户数据
                    var Login_Sid = Convert.ToString(dr["Sid"]);
                    this.lbl_number.Text = Login_Sid;
                }
            }
            catch (Exception ex)
            {
                MessageBox.Show("数据库异常，请联系管理员        " + ex.Message);
            }
            finally
            {
                coon.Close();
            }
        }
        #endregion
        #region  删除将要提交的用户成绩
        /// <summary>
        /// 删除要提交的数据
        /// </summary>
        /// <param name="sender"></param>
        /// <param name="e"></param>
        private void Delete_Infomation_Click(object sender, EventArgs e)
        {
            if (this.dataGridView1.Rows.Count < 1)
            {
                return;
            }
            this.dataGridView1.Rows.RemoveAt(0);
        }
        #endregion
    }
}
